package com.xishu.dao.jpa;

import com.xishu.entity.order.OrderCatalogMapping;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.Tuple;
import java.util.List;

public interface OrderCatalogMappingDao extends JpaRepository<OrderCatalogMapping, Long>, JpaSpecificationExecutor<OrderCatalogMapping> {

    /**
     * 年月日选项，带中文名称集合查询金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param catalogName_zh
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , catalog_id as tmpId, catalog_name as y, catalog_name_zh as y_zh,  catalog_name_en as y_en , sum(cash) as value " +
            "from order_catalog_mapping m " +
            "where m.shop_id in (:shopIdList) " +
            "and m.catalog_name_zh in (:catalogName_zh)" +
            "and m.catalog_count > 0 " +
            " and m.catalog_id > 0 " +
            "and m.create_time >= :startTime and m.create_time <= :endTime  and flag_delete=false " +
            "group by m.catalog_name_zh,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> reportZh(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit, List<String> catalogName_zh);

    /**
     * 年月日选项，带英文名称集合查询金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param catalogName_en
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , catalog_id as tmpId, catalog_name as y, catalog_name_zh as y_zh,  catalog_name_en as y_en , sum(cash) as value " +
            "from order_catalog_mapping m " +
            "where m.shop_id in (:shopIdList) " +
            "and m.catalog_name_en in (:catalogName_en)" +
            "and m.catalog_count > 0 " +
            " and m.catalog_id > 0 " +
            "and m.create_time >= :startTime and m.create_time <= :endTime  and flag_delete=false " +
            "group by m.catalog_name_en,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> reportEn(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit, List<String> catalogName_en);

    /**
     * 年月日选项，不带名称集合查询金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , catalog_id as tmpId, catalog_name as y, catalog_name_zh as y_zh,  catalog_name_en as y_en , sum(cash) as value " +
            "from order_catalog_mapping m " +
            "where m.shop_id in (:shopIdList) " +
            "and m.catalog_count > 0 " +
            " and m.catalog_id > 0 " +
            "and m.create_time >= :startTime and m.create_time <= :endTime  and flag_delete=false " +
            "group by m.catalog_name_zh,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> report(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit);

    /**
     * 年月日选项，带中文名称集合查询数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param catalogName_zh
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , catalog_id as tmpId, catalog_name as y, catalog_name_zh as y_zh,  catalog_name_en as y_en , sum(catalog_count) as value " +
            "from order_catalog_mapping m " +
            "where m.shop_id in (:shopIdList) " +
            "and m.catalog_name_zh in (:catalogName_zh)" +
            "and m.catalog_count > 0 " +
            " and m.catalog_id > 0 " +
            "and m.create_time >= :startTime and m.create_time <= :endTime  and flag_delete=false " +
            "group by m.catalog_name_zh,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> reportQtyZh(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit, List<String> catalogName_zh);


    /**
     * 年月日选项，带英文名称集合查询数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param catalogName_en
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , catalog_id as tmpId, catalog_name as y, catalog_name_zh as y_zh,  catalog_name_en as y_en , sum(catalog_count) as value " +
            "from order_catalog_mapping m " +
            "where m.shop_id in (:shopIdList) " +
            "and m.catalog_name_en in (:catalogName_en)" +
            "and m.catalog_count > 0 " +
            " and m.catalog_id > 0 " +
            "and m.create_time >= :startTime and m.create_time <= :endTime  and flag_delete=false " +
            "group by m.catalog_name_en,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> reportQtyEn(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit, List<String> catalogName_en);


    /**
     * 年月日选项，不带名称集合查询数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , catalog_id as tmpId, catalog_name as y, catalog_name_zh as y_zh,  catalog_name_en as y_en , sum(catalog_count) as value " +
            "from order_catalog_mapping m " +
            "where m.shop_id in (:shopIdList) " +
            "and m.catalog_count > 0 " +
            "and m.create_time >= :startTime and m.create_time <= :endTime  and flag_delete=false " +
            " and m.catalog_id > 0 " +
            "group by m.catalog_name_zh,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> reportQty(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit);

    @Query("select  catalogName as y, sum(cash) as value , count(*) as count from order_catalog_mapping m where m.shopId =:shopId and m.createTime >= :startTime and m.createTime <= :endTime and m.catalogId > 0 group by m.catalogId")
    public List<Tuple> count(Long shopId, Long startTime, Long endTime);


    /**
     * 总计选项，带品类中文名称查金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param catalogName_zh
     * @return
     */
    @Query("select catalogId as tmpId,sum(cash) as value, catalogName as y, catalogName_zh as y_zh, catalogName_en as y_en " +
            "from order_catalog_mapping m " +
            "where m.createTime >= :startTime and m.createTime <= :endTime " +
            "and m.shopId in (:shopIdList)" +
            "and m.catalogName_zh in (:catalogName_zh)" +
            "and m.catalogCount > 0 " +
            "and m.catalogId > 0 " +
            "group by m.catalogName_zh " +
            "order by sum(cash) desc")
    public List<Tuple> idTotalAmountZh(List<Long> shopIdList, Long startTime, Long endTime, List<String> catalogName_zh);

    /**
     * 总计选项，带品类英文名称查金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param catalogName_en
     * @return
     */
    @Query("select catalogId as tmpId,sum(cash) as value, catalogName as y, catalogName_zh as y_zh, catalogName_en as y_en " +
            "from order_catalog_mapping m " +
            "where m.createTime >= :startTime and m.createTime <= :endTime " +
            "and m.shopId in (:shopIdList)" +
            "and m.catalogName_en in (:catalogName_en)" +
            "and m.catalogCount > 0 " +
            "and m.catalogId > 0 " +
            "group by m.catalogName_en " +
            "order by sum(cash) desc")
    public List<Tuple> idTotalAmountEn(List<Long> shopIdList, Long startTime, Long endTime, List<String> catalogName_en);



    /**
     * 总计选项，不带品类名称集合查金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select catalogId as tmpId,sum(cash) as value, catalogName as y, catalogName_zh as y_zh, catalogName_en as y_en " +
            "from order_catalog_mapping m " +
            "where m.shopId in (:shopIdList) and m.createTime >= :startTime and m.createTime <= :endTime " +
            "and m.catalogCount > 0 " +
            "and m.catalogId > 0 " +
            "group by catalogId " +
            "order by sum(cash) desc")
    public List<Tuple> totalAmount(List<Long> shopIdList, Long startTime, Long endTime);



    /**
     * 总计选项，带品类中文名称集合查数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param catalogName_zh
     * @return
     */
    @Query("select catalogId as tmpId,sum(catalogCount) as value, catalogName as y, catalogName_zh as y_zh, catalogName_en as y_en " +
            "from order_catalog_mapping m " +
            "where m.createTime >= :startTime and m.createTime <= :endTime " +
            "and m.shopId in (:shopIdList)"+
            "and m.catalogName_zh in (:catalogName_zh)" +
            "and m.catalogCount >0 " +
            "and m.catalogId > 0 " +
            "group by m.catalogName_zh " +
            "order by sum(catalogCount) desc")
    List<Tuple> idTotalQtyZh(List<Long> shopIdList, Long startTime, Long endTime, List<String> catalogName_zh);

    /**
     * 总计选项，带品类英文名称集合查数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param catalogName_en
     * @return
     */
    @Query("select catalogId as tmpId,sum(catalogCount) as value, catalogName as y, catalogName_zh as y_zh, catalogName_en as y_en " +
            "from order_catalog_mapping m " +
            "where m.createTime >= :startTime and m.createTime <= :endTime " +
            "and m.catalogName_en in (:catalogName_en)" +
            "and m.catalogCount > 0 " +
            "and m.shopId in (:shopIdList)"+
            "and m.catalogId > 0 " +
            "group by m.catalogName_en " +
            "order by sum(catalogCount) desc")
    List<Tuple> idTotalQtyEn(List<Long> shopIdList, Long startTime, Long endTime, List<String> catalogName_en);


    /**
     * 总计选项，带品类英文名称集合查数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select catalogId as tmpId,sum(catalogCount) as value, catalogName as y, catalogName_zh as y_zh, catalogName_en as y_en " +
            "from order_catalog_mapping m " +
            "where m.shopId in (:shopIdList) and m.createTime >= :startTime and m.createTime <= :endTime " +
            "and m.catalogCount > 0 " +
            "and m.catalogId > 0 " +
            "group by catalogId " +
            "order by sum(catalogCount) desc")
    List<Tuple> TotalQty(List<Long> shopIdList, Long startTime, Long endTime);

    @Query(value = "select catalog_name_zh as name_zh,catalog_name_en as name_en from order_catalog_mapping m " +
            "where m.catalog_count > 0 and m.shop_id =:shopId and catalog_id > 0  group by catalog_name_zh, catalog_name_en", nativeQuery = true)
    public List<Tuple> shopSearchItem(Long shopId);
}
